package com.example.excel.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.export.ExcelBatchExportService;
import com.example.excel.data.*;
import com.example.excel.service.impl.ExcelBatchExportServiceImpl;
import com.example.excel.service.impl.ExcelExportStylerDetailImpl;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.math.BigDecimal;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.function.Consumer;

/**
 * @author Aaron
 * @since 2021/7/20 15:58
 * <p>描述：</p>
 */
public class EasyPoiUtil {

    public static class LongRentCar {

        /**
         * 长租车第二行标题
         */
        public static final String SECOND_TITLE = "" +
                "1、车牌号、车辆类型、生效时间、失效时间为必填项\n" +
                "2、车牌号真实有效\n" +
                "3、失效时间大于生效时间\n" +
                "4、停车场同一区域内不能出现车牌号、车辆类型同时相同的长租车数据";

        /**
         * 长租车 excel 导出
         *
         * @param dataSet  数据集
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void normalDataExport(HttpServletRequest request, HttpServletResponse response,
                                            List<LongRentCarDto> dataSet, boolean isIe) throws IOException {
            dataSet.add(0, getExampleLongRentCarDto().get(0));
            doExcelExport("长租车管理", SECOND_TITLE, 25, "长租车", LongRentCarDto.class,
                    dataSet, request, response, "长租车管理", EasyPoiUtil.LongRentCar::freeze, isIe);
        }

        /**
         * 长租车 excel 导出
         *
         * @param dataSet  数据集
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void failedDataExport(HttpServletRequest request, HttpServletResponse response,
                                            List<LongRentCarVo> dataSet, boolean isIe) throws IOException {
            dataSet.add(0, getExampleLongRentCarVo().get(0));
            doExcelExport("长租车导入失败数据", SECOND_TITLE, 25, "长租车", LongRentCarVo.class,
                    dataSet, request, response, "长租车导入失败数据", EasyPoiUtil.LongRentCar::freeze, isIe);
        }

        /**
         * 长租车模板下载
         *
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void downloadTemplate(HttpServletRequest request, HttpServletResponse response, boolean isIe) throws IOException {
            doExcelExport("长租车导入模板", SECOND_TITLE, 25, "长租车", LongRentCarDto.class,
                    getExampleLongRentCarDto(), request, response, "长租车导入模板", EasyPoiUtil.LongRentCar::freeze, isIe);
        }

        private static List<LongRentCarDto> getExampleLongRentCarDto() {
            List<LongRentCarDto> templateData = new ArrayList<>();
            templateData.add(LongRentCarDto.builder().parkName("(示例勿删)市民中心").carOwnerName("张三")
                    .carOwnerTel("13068889888").address("中岳大街").companyName("二大队").carNum("豫A12345")
                    .vehicleType("1").beginDateStr("2018-09-01").endDateStr("2022-07-30").fee("200").build());
            return templateData;
        }

        private static List<LongRentCarVo> getExampleLongRentCarVo() {
            List<LongRentCarVo> templateData = new ArrayList<>();
            templateData.add(LongRentCarVo.builder().carOwnerName("(示例勿删)张三")
                    .carOwnerTel("13068889888").address("中岳大街").companyName("二大队").carNum("豫A12345")
                    .vehicleType("1").beginDateStr("2018-09-01").endDateStr("2022-07-30").fee("200")
                    .build());
            return templateData;
        }

        /**
         * 长租车 excel 导入
         *
         * @param file 上传的 excel 文件
         * @return 读取到的数据集合
         * @throws Exception 异常
         */
        public static List<LongRentCarVo> excelImport(MultipartFile file) throws Exception {
            List<LongRentCarVo> longRentCarVos = EasyPoiUtil.excelImport(file, LongRentCarVo.class, 2, 1);
            // 删除示例数据
            longRentCarVos.remove(0);

            return longRentCarVos;
        }

        private static void freeze(Workbook workbook) {
            // 冻结设置
            Sheet sheet = workbook.getSheetAt(0);
            sheet.createFreezePane(0, 3);
        }

    }

    public static class VehicleGroup {

        /**
         * 固定用户组第二行标题
         */
        public static final String SECOND_TITLE = "" +
                "1、用户名称、手机号、车牌号、车辆类型、生效时间、失效时间为必填项\n" +
                "2、车牌号真实有效\n" +
                "3、失效时间大于生效时间\n" +
                "4、同一停车场内一个车牌号仅能属于一个固定用户组，且该车牌不可同时存在于长租车和固定用户组";

        /**
         * 固定用户组 excel 导出
         *
         * @param dataSet  数据集
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void normalDataExport(HttpServletRequest request, HttpServletResponse response,
                                            List<VehicleDto> dataSet, boolean isIe) throws IOException {
            dataSet.add(0, getExampleVehicleDto().get(0));
            doExcelExport("固定用户组", SECOND_TITLE, 25, "固定用户", VehicleDto.class,
                    dataSet, request, response, "固定用户组", EasyPoiUtil.VehicleGroup::freeze, isIe);
        }

        /**
         * 固定用户组 excel 导出
         *
         * @param dataSet  数据集
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void failedDataExport(HttpServletRequest request, HttpServletResponse response,
                                            List<VehicleVo> dataSet, boolean isIe) throws IOException {
            dataSet.add(0, getExampleVehicleVo().get(0));
            doExcelExport("固定用户组导入失败数据", SECOND_TITLE, 25, "固定用户", VehicleVo.class,
                    dataSet, request, response, "固定用户组导入失败数据", EasyPoiUtil.VehicleGroup::freeze, isIe);
        }

        /**
         * 固定用户组模板下载
         *
         * @param request  HttpServletRequest
         * @param response HttpServletResponse
         * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
         */
        public static void downloadTemplate(HttpServletRequest request, HttpServletResponse response, boolean isIe) throws Exception {
            // 用户组模板数据
            List<VehicleDto> templateData = getExampleVehicleDto();

            doExcelExport("固定用户组导入模板", SECOND_TITLE, 25, "固定用户", VehicleDto.class,
                    templateData, request, response, "用户组导入模板", EasyPoiUtil.VehicleGroup::freeze, isIe);
        }

        private static List<VehicleDto> getExampleVehicleDto() {
            List<VehicleDto> templateData = new ArrayList<>();
            VehicleDto vehicleDto = new VehicleDto();
            // 车辆名单信息集
            List<TbVehicleListDto> tbVehicleListDtoList = new ArrayList<>(1);

            // 车主信息
            vehicleDto.setTbVehicleListDtos(tbVehicleListDtoList);
            vehicleDto.setParkName("（示例勿删）市民中心");
            vehicleDto.setOwnerName("张三");
            vehicleDto.setOwnerPhone("13068889888");
            vehicleDto.setOwnerAddress("中岳大街");
            vehicleDto.setOwnerCompany("二大队");

            // 车辆信息
            TbVehicleListDto tbVehicleListDto = new TbVehicleListDto();
            tbVehicleListDto.setCarNum("豫A12345");
            tbVehicleListDto.setVehicleType("1");
            tbVehicleListDto.setBeginTimeStr("2018-09-01");
            tbVehicleListDto.setEndTimeStr("2022-07-30");
            tbVehicleListDto.setFee(new BigDecimal("200"));
            tbVehicleListDtoList.add(tbVehicleListDto);

            templateData.add(vehicleDto);
            return templateData;
        }

        private static List<VehicleVo> getExampleVehicleVo() {
            List<VehicleVo> templateData = new ArrayList<>();
            VehicleVo vehicleVo = new VehicleVo();
            // 车辆名单信息集
            List<TbVehicleListDto> tbVehicleListDtoList = new ArrayList<>(1);
            vehicleVo.setTbVehicleListDtos(tbVehicleListDtoList);
            TbVehicleListDto tbVehicleListDto = new TbVehicleListDto();
            tbVehicleListDtoList.add(tbVehicleListDto);

            // 车主信息
            vehicleVo.setParkName("（示例勿删）\n市民中心");
            vehicleVo.setOwnerName("张三");
            vehicleVo.setOwnerPhone("13068889888");
            vehicleVo.setOwnerAddress("中岳大街");
            vehicleVo.setOwnerCompany("二大队");
            // 车辆信息
            tbVehicleListDto.setCarNum("豫A12345");
            tbVehicleListDto.setVehicleType("1");
            tbVehicleListDto.setBeginTimeStr("2018-09-01");
            tbVehicleListDto.setEndTimeStr("2022-07-30");
            tbVehicleListDto.setFee(new BigDecimal("200"));

            templateData.add(vehicleVo);
            return templateData;
        }

        /**
         * 固定用户组 excel 导入
         *
         * @param file 上传的 excel 文件
         * @return 读取到的数据集合
         * @throws Exception 异常
         */
        public static List<VehicleVo> excelImport(MultipartFile file) throws Exception {
            List<VehicleVo> vehicleVos = EasyPoiUtil.excelImport(file, VehicleVo.class, 2, 2);
            // 删除示例数据
            vehicleVos.remove(0);

            return vehicleVos;
        }

        private static void freeze(Workbook workbook) {
            // 冻结设置
            Sheet sheet = workbook.getSheetAt(0);
            sheet.createFreezePane(0, 4);
        }

    }

    /**
     * Excel 导入
     *
     * @param file      导入的 Excel 文件
     * @param clazz     表格映射类的 Class
     * @param titleRows Excel 表头所占行数
     * @param headRows  Excel 表的标题行所占行数
     * @param <T>       泛型
     * @return 读取到的数据集合
     * @throws Exception 异常
     */
    public static <T> List<T> excelImport(MultipartFile file, Class<T> clazz, Integer titleRows, Integer headRows) throws Exception {
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headRows);
        return ExcelImportUtil.importExcel(file.getInputStream(), clazz, params);
    }

    /**
     * excel 导出
     *
     * @param title             标题
     * @param secondTitle       第二行标题
     * @param secondTitleHeight 第二行标题高度
     * @param sheetName         sheet 名称
     * @param pojoClass         导出数据映射 Class
     * @param dataSet           数据集
     * @param request           HttpServletRequest
     * @param response          HttpServletResponse
     * @param fileName          导出文件名称
     * @param consumer          对 Workbook 中的 sheet、cell 等的特殊处理
     * @param isIe              是否是 IE 浏览器
     * @throws IOException 写入输出流或关闭 workbook 时抛出 IO 异常
     */
    public static <T> void doExcelExport(String title, String secondTitle, Integer secondTitleHeight, String sheetName,
                                         Class<T> pojoClass, Collection<T> dataSet,
                                         HttpServletRequest request,
                                         HttpServletResponse response, String fileName,
                                         Consumer<Workbook> consumer, boolean isIe) throws IOException {
        // 标题，第二标题、sheet 名称
        ExportParams exportParams = new ExportParams(title, secondTitle, sheetName);
        // 第二标题高度
        exportParams.setSecondTitleHeight(secondTitleHeight.shortValue());
        // Excel 样式
        exportParams.setStyle(ExcelExportStylerDetailImpl.class);
        // 继承 ExcelBatchExportService，以便修改第二标题样式
        ExcelBatchExportService batchService = ExcelBatchExportServiceImpl.
                getExcelBatchExportService(exportParams, pojoClass);
        // 添加要导出的数据
        Workbook workbook = batchService.appendData(dataSet);
        // 对 Workbook 中的 sheet、cell 等的特殊处理
        consumer.accept(workbook);
        // 适配浏览器
        if (isIe(request) || isIe) {
            fileName = java.net.URLEncoder.encode(fileName, "UTF8");
        } else {
            fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
        }

        response.setHeader("Content-Disposition", "attachment;filename = " + fileName + ".xlsx");
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        workbook.close();
        ExcelExportUtil.closeExportBigExcel();
    }

    protected static boolean isIe(HttpServletRequest request) {
        return request.getHeader("USER-AGENT").toLowerCase().indexOf("msie") > 0
                || request.getHeader("USER-AGENT").toLowerCase().indexOf("rv:11.0") > 0
                || request.getHeader("USER-AGENT").toLowerCase().indexOf("edge") > 0;
    }

}
